package com.bmts.gtmc.xls;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class DBExcelOperation {
	
	private static Log log = LogFactory.getLog(DBExcelOperation.class) ;
	
	//#执行导出或导入操作（E为导出操作，I为导入操作）
	private static String ep_im ;
	//数据库连接信息（数据导出方）
	private static String exportdbdriver ;
	private static String exportdburl ;
	private static String exportdbusername ;
	private static String exportdbpassword ;
	//数据库操作，编写SQL与进行数据筛选
	private static String exportsql ;
	//导出表中的哪些列（不能为不写）","逗号分割
	private static String exportcolumn ;
	//设置工作本,不写则默认为sheet1
	private static String set_sheet ;
	//是否设置标题行,Y为设置，N为不设置
	private static String set_titlerow ;
	//设置标题行对应的列，如果为空则默认使用序号为（3）的作为默认列 
	private static String set_titlecell ;
	//文件导出位置，及文件名称。只给文件名称，则是当前路径
	private static String excel_exportfile_path ;
	
	//excel数据文件位置
	private static String import_excelpath ;
	//数据库连接信息（数据导入方
	private static String importdbdriver ;
	private static String importdburl ;
	private static String importdbusername ;
	private static String importdbpassword ;
	//数据库操作，编写数据插入SQL（INSERT INTO table(a,b,c) VALUES(?,?,?)）
	private static String importinsert_sql ;
	//SQL插入的列数是否与excel中的列数是否一致，Y是，N否，
	//如果为N，则需要设置import.excel_col.num来获取excel中的哪些列填充SQL语句，给出列号（0表示第一列）","逗号分割
	private static String importexcel_colsql_col ;
	private static String importexcel_colnum ;
	//是否跳过标题,Y跳过，N不跳过
	private static String importskiptitle ;
	//获得哪个工作表,不写则默认为sheet1，多个","逗号分割
	private static String importset_sheet ;
	
	
	//初始化数据库连接
	private static Connection conn ;
	
	static {
		log.info("初始化配置数据信息") ;
		ep_im = ProUtil.getBean("ep_im") ;
		exportdbdriver = ProUtil.getBean("export.db.driver") ;
		exportdburl = ProUtil.getBean("export.db.url") ;
		exportdbusername = ProUtil.getBean("export.db.username") ;
		exportdbpassword = ProUtil.getBean("export.db.password") ;
		exportsql = ProUtil.getBean("export.sql") ;
		set_sheet = ProUtil.getBean("set_sheet") ;
		exportcolumn = ProUtil.getBean("export.column") ;
		set_titlerow = ProUtil.getBean("set_title.row") ;
		set_titlecell = ProUtil.getBean("set_title.cell") ;
		excel_exportfile_path = ProUtil.getBean("excel_export.file_path") ;
		
		import_excelpath = ProUtil.getBean("import_excel.path") ;
		importdbdriver = ProUtil.getBean("import.db.driver") ;
		importdburl = ProUtil.getBean("import.db.url") ;
		importdbusername = ProUtil.getBean("import.db.username") ;
		importdbpassword = ProUtil.getBean("import.db.password") ;
		importinsert_sql = ProUtil.getBean("import.insert_sql") ;
		importexcel_colsql_col = ProUtil.getBean("import.excel_col.sql_col") ;
		importexcel_colnum = ProUtil.getBean("import.excel_col.num") ;
		importskiptitle = ProUtil.getBean("import.skip.title") ;
		importset_sheet = ProUtil.getBean("import.set_sheet") ;
	}
	
	public static void initconfig() {
		
		log.info("数据库初始化连接") ;
		
		String[] ep_im_sp = configSpli(ep_im) ;
		
		//数据库连接
		if(null != ep_im_sp) {
			for (int i = 0; i < ep_im_sp.length; i++) {
				if("E".equals(ep_im_sp[i])) {
					log.info("连接导出方数据库") ;
					try {
						Class.forName(exportdbdriver) ;
						conn = DriverManager.getConnection(exportdburl, exportdbusername,exportdbpassword) ;
						
						long startTime = System.currentTimeMillis(); //获取开始时间   
						dbExportExcel() ;	//调用导出方法
						long endTime=  System.currentTimeMillis(); //获取结束时间   
						log.info("数据导出运行时间： "+(endTime-startTime)+"ms") ;
						
					} catch (ClassNotFoundException e) {
						log.info("数据库连接失败，找不到驱动类：" + e.getMessage()) ;
					} catch (SQLException e) {
						log.info("数据库操作错误：" + e.getMessage()) ;
					}
				} 
				if("I".equals(ep_im_sp[i])) {
					log.info("连接导入方数据库") ;
					try {
						Class.forName(importdbdriver) ;
						conn = DriverManager.getConnection(importdburl, importdbusername,importdbpassword) ;
						
						long startTime = System.currentTimeMillis(); //获取开始时间   
						dbImportExcel() ;	//调用导入方法
						long endTime=  System.currentTimeMillis(); //获取结束时间   
						log.info("数据导入运行时间： "+(endTime-startTime)+"ms") ;
						
					} catch (ClassNotFoundException e) {
						log.info("数据库连接失败，找不到驱动类：" + e.getMessage()) ;
						e.printStackTrace();
					} catch (SQLException e) {
						log.info("数据库操作错误：" + e.getMessage()) ;
					}
				}
			}
		} else {
			log.info("配置文件[key=ep_im]错误！") ;
		}
	}
	
	public static String[] configSpli(String param) {
		return (null!= param && !"".equals(param)?param.split(","):null) ;
	}
	
	/**
	 * 数据导出
	 */
	public static void dbExportExcel() {
		log.info("数据开始导出...") ;
		
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		try {
			//初始化行数
			int rownum = 0 ;
			
			pstmt = conn.prepareStatement(exportsql) ;
			rs = pstmt.executeQuery() ;
			log.info("执行SQL：" + exportsql) ;
			
			//构建Excel实例
			Workbook wb = new HSSFWorkbook() ;
			
			//创建工作表
			String[] set_sheet_sp = null == configSpli(set_sheet)?new String[]{"sheet1"}:configSpli(set_sheet) ;
			
			if(null != set_sheet_sp && set_sheet_sp.length > 0) {
				for (int i = 0; i < set_sheet_sp.length; i++) {
					//创建工作表
					Sheet sheet = wb.createSheet(set_sheet_sp[i]) ;
					
					//设置标题列
					if("Y".equals(set_titlerow)) {
						log.info("设置标题列:" + set_titlecell) ;
						String[] set_titlecell_sp = (null == configSpli(set_titlecell)?configSpli(exportcolumn):configSpli(set_titlecell)) ; 
						
						Row r = sheet.createRow(0) ;
						
						for (int j = 0; j < set_titlecell_sp.length; j++) {
							Cell c = r.createCell(j) ;
							c.setCellValue(set_titlecell_sp[j]) ;
						}
						rownum = 1 ;
					}
					
					while(rs.next()) {
						Row r = sheet.createRow(rownum) ;
						
						//获得要取的列
						String[] exportcolumn_sp = configSpli(exportcolumn) ;
						if(null != exportcolumn_sp && exportcolumn_sp.length > 0) {
							
							for (int j = 0; j < exportcolumn_sp.length; j++) {
								Cell c = r.createCell(j) ;
								c.setCellValue((null == rs.getString(exportcolumn_sp[j])||"".equals(rs.getString(exportcolumn_sp[j]))?"":rs.getString(exportcolumn_sp[j]))) ;
							}
							
						} else {
							log.info("配置文件，导出表中的哪些列[key=export.column]错误！");
							return ;
						}
						rownum = rownum + 1 ;
 					}
				}
			}
			
			FileOutputStream fileOut = new FileOutputStream(excel_exportfile_path) ;
			wb.write(fileOut) ;
			fileOut.flush() ;
			fileOut.close() ;
			
			log.info("数据导出完成，总行数[" + rownum + "]文件名称：" + excel_exportfile_path) ;
			
		} catch (SQLException e) {
			log.info("数据库操作错误:" + e.getMessage()) ;
		} catch (FileNotFoundException e) {
			log.info("文件生成失败：" + e.getMessage()) ;
		} catch (IOException e) {
			log.info("文件操作发生错误：" + e.getMessage()) ;
		} finally {
			try {
				if(null != rs) {
					rs.close() ;
					rs = null ;
				}
				if(null != pstmt) {
					pstmt.close() ;
				}
			} catch (SQLException e) {
				log.info("数据库操作关闭出错:" + e.getMessage()) ;
			}
		}
	}
	
	/**
	 * 数据导入
	 */
	public static void dbImportExcel() {
		log.info("数据开始导入...") ;
		PreparedStatement pstmt = null ;
		try {
			pstmt = conn.prepareStatement(importinsert_sql) ;
			log.info("执行SQL：" + importinsert_sql) ;
			
			//读取excel数据文件
			FileInputStream fis = new FileInputStream(import_excelpath) ;
			log.info("加载Excel数据文件：" + import_excelpath) ;
			
			//构建Workbook
			Workbook wb = new HSSFWorkbook(new POIFSFileSystem(fis)) ;
			
			//获得工作表
			String[] importset_sheet_sp = null == configSpli(importset_sheet)?new String[]{"sheet1"}:configSpli(importset_sheet) ;
			if(null != importset_sheet_sp && importset_sheet_sp.length > 0) {
				for (int j = 0; j < importset_sheet_sp.length; j++) {
					
					Sheet sheet = wb.getSheet(importset_sheet_sp[j]) ;
					
					//总行数
					int totalRow = sheet.getPhysicalNumberOfRows() ;
					//设置开始行,默认跳过第一行（标题行）
					int startRow = 1 ;
					//不跳过过标题行
					if("N".equals(importskiptitle)) {	
						totalRow = 0 ;
						log.info("读取工作表["+sheet.getSheetName()+"] 不忽略标题行 - 总行数["+totalRow+"]") ;
					}
					log.info("读取工作表["+sheet.getSheetName()+"] - 忽略标题行 - 总行数["+(totalRow-startRow)+"]") ;
					
					//循环取出行数
					for (int i = startRow; i < totalRow; i++) {
						Row r = sheet.getRow((short)i) ;
						
						//excel文件中的列是否与SQL插入的列是否一致,不一致则获取指定列
						//指定列
						if("N".equals(importexcel_colsql_col)) {
							String[] chooseCol = configSpli(importexcel_colnum) ;
							if(null != chooseCol) {
								for (int k = 0; k < chooseCol.length; k++) {
									Cell cell = r.getCell(Short.parseShort(chooseCol[k])) ;
									//System.out.print(cell.getStringCellValue()+"\t");
									pstmt.setObject(k+1, cell.getStringCellValue()) ;
								}
							} else {
								log.info("配置文件指定列错误[key=import.excel_col.num]");
							}
						} else {
							//循环取出每行的列
							for (int k = 0; k < r.getPhysicalNumberOfCells(); k++) {
								Cell cell = r.getCell((short)k) ;
								//System.out.print(cell.getStringCellValue()+"\t");
								pstmt.setObject(k+1, cell.getStringCellValue()) ;
							}
						}
						//System.out.println("");
						pstmt.execute() ;
					}
				}
			}
		} catch (FileNotFoundException e) {
			log.info("无法找到类：" + e.getMessage()) ;
		} catch (IOException e) {
			log.info("文件操作失败：" + e.getMessage()) ;
		} catch (SQLException e) {
			log.info("数据库操作错误:" + e.getMessage()) ;
		} finally {
			if(null != pstmt) {
				try {
					pstmt.close() ;
				} catch (SQLException e) {
					log.info("PreparedStatement对象关闭出错：" + e.getMessage()) ;
				} 
			}
		}
	}
	
	public static String encodeURI(String str) {
		try {
			return new String(str.getBytes("ISO-8859-1"),"UTF-8") ;
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return "" ;
	}
	
}
